﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.Data;

namespace AdatbazisProjekt
{
    public struct userStruct
    {
        public   List<string> username;
        public    List<string> userpriv;
    };
    public struct buyerStruct{
        public List<string> szemszam;
        public List<string> nev;
        public List<string> telo;
        public List<string> bank;
        public List<string> bankszam;
        public List<string> regcom;
        public List<string> cim;
    };
    
    class UserDal:DalGen
    {
        bool e;
        public UserDal()
        {
            if (!base.IsConnectCreated())
            {
             bool   Err = !base.MakeConnection();
            }
        }
        public userStruct getUserName()
        {
          

            // visszateriti az alkatreszek neveit melyek egy adott tablaban csoporosulnak lasd pl Processzorok
            userStruct pro = new userStruct();
            try
            {
                string error = "";
                string cmd = "select username,userpriviledge from userek";
                SqlDataReader reader = mExecuteReader(cmd, ref error, null);
                List<string> sanyi =new List<string>();
                List<string> bela=new List<string>();
                while (reader.Read())
                {
                    
                    sanyi.Add( reader["Username"].ToString());
                    bela.Add( reader["Userpriviledge"].ToString());
                    
                }
                pro.username = sanyi;
                pro.userpriv = bela;
                this.CloseConnection();
                return pro;
            }
            catch (Exception)
            {
              
                return pro;
            }
        }
        public void userUpdate(string nev, string rank) {
            
            MyExecuteNonQuery("update userek set userpriviledge="+rank+" where username='"+nev+"' ",ref e,null);
           // MessageBox.Show(e.ToString());
            this.CloseConnection();
        }
        public void insertUser(string nev, string rank, string passwd) {
            string err = "";
            SqlDataReader reader = mExecuteReader("SELECT * FROM Userek WHERE username = '" + nev + "'", ref err, null);
            if(reader.Read())
            {
                   MessageBox.Show("Hiba a felhasznalo mar letezik!");
            }
            else
            {
                MyExecuteNonQuery("exec Insertuser '"+nev+"',"+rank+",'"+passwd+"'", ref e, null);
            }
            this.CloseConnection();
        }
        public void deleteUser(string nev) {
            MyExecuteNonQuery("delete from userek where username='"+nev+"'", ref e, null);
            this.CloseConnection();
        }
        public buyerStruct getBuyerSimilar(string nev) { 
            
            // visszateriti az alkatreszek neveit melyek egy adott tablaban csoporosulnak lasd pl Processzorok
            buyerStruct pro = new buyerStruct();
            try
            {
                string error = "";
                string cmd = "select * from vasarlo where nev like '"+ nev+"%'";
                SqlDataReader reader = mExecuteReader(cmd, ref error, null);
                List<string> zoli =new List<string>();
                List<string> karoly=new List<string>();
                List<string> gyuszi =new List<string>();
                List<string> sandor=new List<string>();
                List<string> balazs =new List<string>();
                List<string> andras=new List<string>();
                List<string> bela=new List<string>();

                while (reader.Read())
                {
                    
                    zoli.Add( reader["szemszam"].ToString());
                    karoly.Add( reader["lakhely"].ToString());
                    gyuszi.Add( reader["telszam"].ToString());
                    sandor.Add( reader["nev"].ToString());
                    balazs.Add( reader["regcom"].ToString());
                    andras.Add( reader["bank"].ToString());
                    bela.Add( reader["bankszamla"].ToString());
                }
                pro.nev=sandor;
                pro.szemszam=zoli;
                pro.telo=gyuszi;
                pro.bank=andras;
                pro.bankszam=bela;
                pro.regcom=balazs;
                pro.cim=karoly;

                this.CloseConnection();
                return pro;
            }
            catch (Exception)
            {
              
                return pro;
            }
        
        
        }

        public string maxord() {
            string pro;
            string error = "";
            string cmd = "select max(nrord)+1 as 'm' from vasarlas";
            SqlDataReader reader = mExecuteReader(cmd, ref error, null);

            if (reader.Read())
            {
                pro = reader["m"].ToString();
            }

            else pro = "53628";
            this.CloseConnection();
            return pro;
        }
    public void xmlproba(){
        try
        {
            DataSet ds = new DataSet();

            DataTable dtOrszagok = QueryCmd("SELECT * FROM Orszagok");
            DataTable dtGyartok = QueryCmd("SELECT * FROM Gyartok");
            DataTable dtTermekek = QueryCmd("SELECT * FROM Termekek");
            DataTable dtUserek = QueryCmd("SELECT * FROM Userek");
    
            DataColumn orszID = dtOrszagok.Columns["orszagID"];
            dtOrszagok.PrimaryKey = new DataColumn[] { orszID };


            DataColumn gyartoID = dtOrszagok.Columns["gyartoID"];
            dtGyartok.PrimaryKey = new DataColumn[] { gyartoID };

            DataColumn termekID = dtOrszagok.Columns["termekID"];
            dtGyartok.PrimaryKey = new DataColumn[] { termekID };

            dtOrszagok.TableName = "orszagok";
            dtGyartok.TableName = "gyartok";
            dtTermekek.TableName = "termekek";
            dtUserek.TableName = "User";


            ds.Tables.Add(dtOrszagok);
            ds.Tables.Add(dtGyartok);
            ds.Tables.Add(dtTermekek);
            ds.Tables.Add(dtUserek);

            DataColumn primarykey = ds.Tables["orszagok"].Columns["orszagID"];

            DataColumn foreignkey = ds.Tables["gyartok"].Columns["orszagID"];

            DataRelation relation = ds.Relations.Add(primarykey, foreignkey);

            relation.Nested = true;    


            DataColumn primarykey2 = ds.Tables["gyartok"].Columns["gyartoID"];

            DataColumn foreignkey2 = ds.Tables["termekek"].Columns["gyartoID"];

            DataRelation relation2 = ds.Relations.Add(primarykey2, foreignkey2);

            relation2.Nested = true;
            
            ds.DataSetName = "Szamitogep_Adatbazis";

            ds.WriteXml("C:/szamla/alldata.xml", System.Data.XmlWriteMode.IgnoreSchema);
            ds.WriteXmlSchema("C:/szamla/xmlschema.xml");

            MessageBox.Show("XML es XSD sikeresen legeneralva a C:/szamla/ konyvtarba!");

        }
        catch (Exception e)
        {
            MessageBox.Show("HIBA!" + e.Message);
        }
    }
    
    }
}
